﻿using Microsoft.Data.SqlClient;
using Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace DAL
{
    public class ProductService
    {

      
        /// <summary>
        /// 根据商品编号获取商品信息
        /// </summary>
        /// <param name="productId">商品编号</param>
        /// <returns>返回商品对象</returns>
        public Product GetProductById(string productId)
        {
            //string sql = "select ProductId,ProductName,UnitPrice,Discount from Products where ProductId=@ProductId";
            string sql = "select * from Products where ProductId=@ProductId";
            SqlDataReader sqlDataReader = SQLHelper.GetReader(sql, new SqlParameter[]
            {
                new SqlParameter("@ProductId", productId)
            });
            Product product = null;
            if (sqlDataReader.Read())
            {
                product = new Product()
                {
                    ProductId = sqlDataReader["ProductId"].ToString(),
                    ProductName = sqlDataReader["ProductName"].ToString(),
                    UnitPrice = Convert.ToDecimal(sqlDataReader["UnitPrice"]),
                    Discount = Convert.ToInt32(sqlDataReader["Discount"])
                };
            }
            sqlDataReader.Close();
            return product;
        }

        /// <summary>
        /// 启用事务提交购买商品对象、更新客户积分
        /// </summary>
        /// <param name="slm">商品销售对象</param>
        /// <param name="member">会员对象</param>
        /// <returns></returns>
        public bool SaveSaleInfo(SalesListMain slm, SMMember member)
        {
            //定义一个List集合，用于保存多条SQL语句
            List<string> sqlList = new List<string>();

            //【1】组合SQL语句（插入主表）
            //StringBuilder sb = new StringBuilder();
            //sb.Append("insert into SalesList (SerialNum, TotalMoney, RealReceive, ReturnMoney, SalesPersonId )");
            //sb.Append(" values ('{0}','{1}', '{2}', '{3}', '{4}' )");

            //sqlList.Add(string.Format(sb.ToString(), ));
            string insertSql = "insert into SalesList (SerialNum, TotalMoney, RealReceive, ReturnMoney, SalesPersonId )";
            insertSql += " values ('{0}',{1}, {2}, {3}, {4}) ";
            insertSql = string.Format(insertSql, slm.SerialNum, slm.TotalMoney, slm.RealReceive, slm.ReturnMoney, slm.SalesPersonId );
            sqlList.Add(insertSql);


            //【2】组合SQL语句（插入明细表，更新库存）
            foreach (SalesListDetail detail in slm.salesLists)
            {
                //sb.Clear();
                //sb.Append("insert into SalesListDetail (Id, SerialNum, ProductId, ProductName, UnitPrice, Discount, Quantity, SubTotalMoney)");
                //sb.Append(" values (@Id, @SerialNum, @ProductId, @ProductName,@ UnitPrice, @Discount, @Quantity, @SubTotalMoney )");
                //SqlParameter[] param2 = new SqlParameter[]
                //{
                //    new SqlParameter("@Id", detail.Id),
                //    new SqlParameter("@SerialNum", detail.SerialNum),
                //    new SqlParameter("@ProductId", detail.ProductId ),
                //    new SqlParameter("@ProductName", detail.ProductName),
                //    new SqlParameter("@UnitPrice", detail.UnitPrice ),
                //    new SqlParameter("@Discount", detail.Discount),
                //    new SqlParameter("@Quantity", detail.Quantity ),
                //    new SqlParameter("@SubTotalMoney", detail.SubTotalMoney),
                //};
                //sqlList.Add(string.Format(sb.ToString(), param2));

                string detailSql = "insert into SalesListDetail (SerialNum, ProductId, ProductName, UnitPrice, Discount, Quantity, SubTotalMoney)";
                detailSql += " values ('{0}','{1}','{2}',{3},{4},{5},{6})";
                detailSql = string.Format(detailSql, detail.SerialNum, detail.ProductId,
                    detail.ProductName, detail.UnitPrice, detail.Discount, detail.Quantity, detail.SubTotalMoney);
                sqlList.Add(detailSql);

                //更新库存
                //sb.Clear();
                //sb.Append("update ProductInventory set TotalCount = TotalCount - @Quantity where ProductId = @ProductId");

                //SqlParameter[] param3 = new SqlParameter[]
                //{
                //    new SqlParameter("@Quantity", detail.Quantity),
                //    new SqlParameter("@ProductId", detail.ProductId)
                //};
                //sqlList.Add(string.Format(sb.ToString(), param3));
                string updateSql = "update ProductInventory set TotalCount = TotalCount - {0} where ProductId = '{1}'";
                updateSql = string.Format(updateSql, detail.Quantity, detail.ProductId);
                sqlList.Add(updateSql);
            }



            //【3】组合SQL语句（更新客户积分）
            if(member != null)
            {
                //sb.Clear();
                //sb.Append("update SMMembers set Points = Points + @Points  where MemberId = @MemberId");
                //SqlParameter[] param4 = new SqlParameter[]
                //{
                //new SqlParameter("@Points", member.Points),
                //new SqlParameter("@TotalMoney", member.MemberId)
                //};
                //sqlList.Add(string.Format(sb.ToString(), param4));

                string pointSql = "update SMMembers set Points = Points + {0}  where MemberId ={1}";
                pointSql = string.Format(pointSql, member.Points, member.MemberId);
                sqlList.Add(pointSql);
            }

            //【4】启用事务执行并返回结果
            return SQLHelper.UpDateByTran(sqlList);

        }
    }
}
